﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows.Forms;  // for exe path of application
using MySql.Data.MySqlClient;     // for connection with database

namespace EventStandsApp
{
    public class DataHelper
    {
        private MySqlConnection connection;

        /* CONSTRUCTOR*/
        public DataHelper()
        {
            
            String connectionInfo = "server=athena01.fhict.local;" +
                             "database=dbi269964;" + // your database name
                             "user id=dbi269964;" + // your user id
                             "password=ztauJSorfR;" + // your password
                             "connect timeout=30;";
            connection = new MySqlConnection(connectionInfo);

        }

        /*returns the maximum order no from the data base*/
        public long getMaxOrderNo()
        {
            String sql = "SELECT MAX(ReceiptNo) FROM HIRE_RECEIPT";
            MySqlCommand command = new MySqlCommand(sql, connection);

            long max=0;

            try
            {
                connection.Open();
                MySqlDataReader reader = command.ExecuteReader();

                while (reader.Read())
                {
                    max= Convert.ToInt64(reader[0]);
                }
            }
            catch (MySqlException err)
            {
                MessageBox.Show("Error: " + err.ToString());
            }
            finally
            {
                connection.Close();
            }
            return max;
        }
        /*
         * returns a list of rented items for a specific client id;
         * if no item will be found, it will return an empty list
         * */
        public List<RentedItem> loadRentedItems(int clientId)
        {
            String visitorId = Convert.ToString(clientId);
            //String sql = "SELECT h.ProductId,h.ProductName,h.UnitPrice,h.Deposit*(l.EndLoan-l.StartLoan),l.HasReturned FROM product_hired h,hire_receipt_line l,hire_receipt r WHERE h.ProductID=l.ProductID AND l.ReceiptNo=r.ReceiptNo AND r.Visitor_id='" + visitorId + "'";
            String sql = "SELECT h.ProductId,h.ProductName,h.UnitPrice,h.Deposit,l.HasReturned FROM product_hired h,hire_receipt_line l,hire_receipt r WHERE h.ProductID=l.ProductID AND l.ReceiptNo=r.ReceiptNo AND r.Visitor_id='" + visitorId + "'";
            MySqlCommand command = new MySqlCommand(sql, connection);

            List<RentedItem> temp;
            temp = new List<RentedItem>();

            try
            {
                connection.Open();
                MySqlDataReader reader = command.ExecuteReader();

                int productId;
                String productName;
                double unitPrice;
                int deposit;
                bool isReturned;
                while (reader.Read())
                {
                    productId = Convert.ToInt32(reader["ProductId"]);
                    productName = Convert.ToString(reader["ProductName"]);
                    unitPrice = Convert.ToDouble(reader["UnitPrice"]);
                    deposit = Convert.ToInt32(reader[3]);
                    String ret = Convert.ToString(reader["HasReturned"]);
                    if (ret == "y") { isReturned = true; } else { isReturned = false; }
                    temp.Add(new RentedItem(productId, productName, unitPrice, deposit, isReturned,0));
                }
            }
            catch (MySqlException err)
            {
                MessageBox.Show("Error: " + err.ToString());
            }
            finally
            {
                connection.Close();
            }
            return temp;
        }
        /*
         * returns a list of rented items
         */
        public List<RentedItem> loadAllRentedItems()
        {
            //String sql = "SELECT h.ProductId,h.ProductName,h.UnitPrice,h.Deposit*(l.EndLoan-l.StartLoan),l.HasReturned FROM product_hired h,hire_receipt_line l,hire_receipt r WHERE h.ProductID=l.ProductID AND l.ReceiptNo=r.ReceiptNo";
            String sql = "SELECT h.ProductId,h.ProductName,h.UnitPrice,h.Deposit,h.ProductsInStock FROM product_hired h";
            MySqlCommand command = new MySqlCommand(sql, connection);

            List<RentedItem> temp;
            temp = new List<RentedItem>();

            try
            {
                connection.Open();
                MySqlDataReader reader = command.ExecuteReader();

                int productId;
                String productName;
                double unitPrice;
                int deposit;
                bool isReturned=false;
                int stock;
                while (reader.Read())
                {
                    productId = Convert.ToInt32(reader["ProductId"]);
                    productName = Convert.ToString(reader["ProductName"]);
                    unitPrice = Convert.ToDouble(reader["UnitPrice"]);
                    deposit = Convert.ToInt32(reader[3]);
                    stock = Convert.ToInt32(reader["ProductsInStock"]);
                    //String ret = Convert.ToString(reader["HasReturned"]);
                    //if (ret == "y") { isReturned = true; } else { isReturned = false; }
                    temp.Add(new RentedItem(productId, productName, unitPrice, deposit, isReturned,stock));
                }
            }
            catch (MySqlException err)
            {
                MessageBox.Show("Error: " + err.ToString());
            }
            finally
            {
                connection.Close();
            }
            return temp;
        }

        /*
         * returns a list of orders for a Client;
         * if no orders found, it will return an empty list
         * */
        public List<Order> getOrders(Client c)
        {
            String visitorId = Convert.ToString(c.ClientId);
            String sql = "SELECT h.ReceiptNo,l.StartLoan,(l.EndLoan-l.StartLoan),h.Price,h.Visitor_id FROM hire_receipt h, hire_receipt_line l WHERE l.ReceiptNo=h.ReceiptNo AND h.Visitor_id='" + visitorId + "'";
            MySqlCommand command = new MySqlCommand(sql, connection);
            List<Order> l = new List<Order>();
            int nr = 0;
            //Order o=null;
            try
            {
                connection.Open();
                MySqlDataReader reader = command.ExecuteReader();

                long orderNo;
                DateTime orderDate;
                int noOfDays;
                double price;
                int clientId; 
                while (reader.Read())
                {
                    orderNo = Convert.ToInt32(reader[0]);
                    orderDate = Convert.ToDateTime(reader[1]);
                    noOfDays = Convert.ToInt32(reader[2]);
                    price = Convert.ToDouble(reader[3]);
                    clientId= Convert.ToInt32(reader[4]);
                   // o= new Order(orderNo,orderDate,c,noOfDays,price);
                    l.Add(new Order(orderNo, orderDate, c, noOfDays, price));
                    nr++;
                }
            }
            catch (MySqlException err)
            {
                MessageBox.Show("Error: " + err.ToString());
            }
            finally
            {
                connection.Close();
            }
            if (nr > 0)
            {
                return l;
            }
            else { return null; }
        }
        /*
         * return a list of rented items for a specidic orderId
         * it will return an empty list if no items are found
         * */
        public List<RentedItem> loadRentedItemsFromOrder(long orderId)
        {
            String id = Convert.ToString(orderId);
            //String sql = "SELECT h.ProductId,h.ProductName,h.UnitPrice,h.Deposit*(l.EndLoan-l.StartLoan),l.HasReturned FROM product_hired h,hire_receipt_line l,hire_receipt r WHERE h.ProductID=l.ProductID AND l.ReceiptNo=r.ReceiptNo AND r.Visitor_id='" + visitorId + "'";
            String sql = "SELECT h.ProductId,h.ProductName,h.UnitPrice,h.Deposit,l.HasReturned FROM product_hired h,hire_receipt_line l,hire_receipt r WHERE h.ProductID=l.ProductID AND l.ReceiptNo=r.ReceiptNo AND r.ReceiptNo='" + id + "'";
            MySqlCommand command = new MySqlCommand(sql, connection);

            List<RentedItem> temp;
            temp = new List<RentedItem>();

            try
            {
                connection.Open();
                MySqlDataReader reader = command.ExecuteReader();

                int productId;
                String productName;
                double unitPrice;
                int deposit;
                bool isReturned;
                while (reader.Read())
                {
                    productId = Convert.ToInt32(reader["ProductId"]);
                    productName = Convert.ToString(reader["ProductName"]);
                    unitPrice = Convert.ToDouble(reader["UnitPrice"]);
                    deposit = Convert.ToInt32(reader[3]);
                    String ret = Convert.ToString(reader["HasReturned"]);
                    if (ret == "y") { isReturned = true; } else { isReturned = false; }
                    temp.Add(new RentedItem(productId, productName, unitPrice, deposit, isReturned, 0));
                }
            }
            catch (MySqlException err)
            {
                MessageBox.Show("Error: " + err.ToString());
            }
            finally
            {
                connection.Close();
            }
            return temp;
        }
        /*
         * returns a list of orderlines after a specific order no
         * return an empty list if no orderlines found*/
        public List<OrderLine> getAllOrderLine(long orderNo)
        {
            String receiptId = Convert.ToString(orderNo);
            String sql = "SELECT l.ReceiptLine,l.StartLoan,l.EndLoan,l.ProductId,l.HasReturned FROM hire_receipt_line l WHERE l.ReceiptNo='" + receiptId + "'";
            MySqlCommand command = new MySqlCommand(sql, connection);

            List<OrderLine> temp;
            temp = new List<OrderLine>();

            try
            {
                connection.Open();
                MySqlDataReader reader = command.ExecuteReader();
                int lineNo;
                DateTime startLoan;
                DateTime endLoan;
                int productId;
                String hasReturned;
                while (reader.Read())
                {
                    lineNo = Convert.ToInt32(reader[0]);
                    startLoan = Convert.ToDateTime(reader[1]);
                    endLoan = Convert.ToDateTime(reader[2]);
                    productId = Convert.ToInt32(reader[3]);
                    hasReturned = Convert.ToString(reader[4]);
                    temp.Add(new OrderLine(orderNo,lineNo,startLoan,endLoan,productId,hasReturned));
                }
            }
            catch (MySqlException err)
            {
                MessageBox.Show("Error: " + err.ToString());
            }
            finally
            {
                connection.Close();
            }
            return temp;
        }
        /*
         * returns a Client object based on client id
         * if no client was found, it will return null*/
        public Client getClient(int clientId)
        {
            String visitorId = Convert.ToString(clientId);
            String sql = "SELECT v.Visitor_id,v.LastName,v.FirstName,e.MoneyDeposited FROM visitor v JOIN event_account e ON v.Visitor_Id=e.Visitor_id WHERE v.Visitor_id='" + visitorId + "'";
            MySqlCommand command = new MySqlCommand(sql, connection);
            int nr = 0;
            List<Client> temp;
            temp = new List<Client>();

            try
            {
                connection.Open();
                MySqlDataReader reader = command.ExecuteReader();

                string name1, name2;
                double balance;
                int no;

                while (reader.Read())
                {
                    no = Convert.ToInt32(reader["Visitor_id"]);
                    name1 = Convert.ToString(reader["LastName"]);
                    name2 = Convert.ToString(reader["FirstName"]);
                    balance = Convert.ToDouble(reader["MoneyDeposited"]);

                    temp.Add(new Client(no, name1, name2, balance));
                    nr++;
                }
            }
            catch (MySqlException err)
            {
                MessageBox.Show("Error: " + err.ToString());
            }
            finally
            {
                connection.Close();
            }
            if (nr > 0)
            {
                return temp[0];
            }
            else { return null; }
        }

        /*
         * adds an order into the database
         * if the operation failed it will return -1, otherwise it will return a positive number*/
        public int addOrder(long orderNo, double price, int standNo, int clientId)
        {

            String receiptNo = Convert.ToString(orderNo);
            String visitorId = Convert.ToString(clientId) ;
            String sql = "INSERT INTO HIRE_RECEIPT VALUES('" + receiptNo + "'," + price + "," + standNo + ",'" + visitorId + "')";
            MySqlCommand command = new MySqlCommand(sql, connection);

            try
            {
                connection.Open();
                //command.Parameters.Add("@receiptNo", receiptNo);
                //command.Parameters.Add("@price", price);
                //command.Parameters.Add("@standNo", standNo);

                int nrOfRecordsChanged = command.ExecuteNonQuery();
                command.Parameters.Clear();
                return nrOfRecordsChanged;
            }
            catch
            {
                return -1; //which means the try-block was not executed succesfully, so  . . .
            }
            finally
            {
                connection.Close();
            }

        }
        /*
         * adds an orderline into the database
         * if the operation failed it will return -1, otherwise it will return a positive number*/
        public int addOrderLine(long orderNo, int lineNo, DateTime start, DateTime end, int prodId, String hasRet)
        {

            String receiptNo = Convert.ToString(orderNo);
            String product = Convert.ToString(prodId);
            String startD = start.ToString("yyyy-MM-dd hh:mm:ss");
            String endD = end.ToString("yyyy-MM-dd hh:mm:ss");
            String sql = "INSERT INTO HIRE_RECEIPT_LINE VALUES('" + receiptNo + "'," + lineNo + ",'" + startD + "','" + endD + "','" + product + "','" + hasRet + "')";
            MySqlCommand command = new MySqlCommand(sql, connection);

            try
            {
                connection.Open();

                int nrOfRecordsChanged = command.ExecuteNonQuery();
                command.Parameters.Clear();
                return nrOfRecordsChanged;
            }
            catch
            {
                return -1; //which means the try-block was not executed succesfully, so  . . .
            }
            finally
            {
                connection.Close();
            }

        }
        /*
         * chenges the HasReturned column into the database
         * if the operation failed it will return -1, otherwise it will return a positive number*/
        public int returnItem(int clientId, RentedItem i)
        {
            String visitorId = Convert.ToString(clientId);
            String prodId = Convert.ToString(i.ProductId);
            String sql = "UPDATE HIRE_RECEIPT_LINE,HIRE_RECEIPT SET HIRE_RECEIPT_LINE.HasReturned='y' WHERE HIRE_RECEIPT.Visitor_id='" + visitorId + "' AND HIRE_RECEIPT_LINE.ProductId='" + prodId + "' AND HIRE_RECEIPT.ReceiptNo=HIRE_RECEIPT_LINE.ReceiptNo"; 
            MySqlCommand command = new MySqlCommand(sql, connection);

            try
            {
                connection.Open();
                int nrOfRecordsChanged = command.ExecuteNonQuery();
                return nrOfRecordsChanged;
            }
            catch
            {
                return -1; //which means the try-block was not executed succesfully, so  . . .
            }
            finally
            {
                connection.Close();
            }
        }
        /*
         * updates the balance of a client into the database
         * if the operation failed it will return -1, otherwise it will return a positive number*/
        public int updateBalace(int clientId, double balance)
        {
            String visitorId = Convert.ToString(clientId);
            String sql = "UPDATE EVENT_ACCOUNT SET MoneyDeposited=" + balance + " WHERE Visitor_id='" + visitorId + "'";
            MySqlCommand command = new MySqlCommand(sql, connection);

            try
            {
                connection.Open();
                int nrOfRecordsChanged = command.ExecuteNonQuery();
                return nrOfRecordsChanged;
            }
            catch
            {
                return -1; //which means the try-block was not executed succesfully, so  . . .
            }
            finally
            {
                connection.Close();
            }
        }
        /*
         * update the amount of money spent of a client into the database
         * if the operation failed it will return -1, otherwise it will return a positive number*/
        public int updateMoneySpent(int clientId, double spent)
        {
            String visitorId = Convert.ToString(clientId);
            String sql = "UPDATE EVENT_ACCOUNT SET MoneySpent=MoneySpent+" + spent + " WHERE Visitor_id='" + visitorId + "'";
            MySqlCommand command = new MySqlCommand(sql, connection);

            try
            {
                connection.Open();
                int nrOfRecordsChanged = command.ExecuteNonQuery();
                return nrOfRecordsChanged;
            }
            catch
            {
                return -1; //which means the try-block was not executed succesfully, so  . . .
            }
            finally
            {
                connection.Close();
            }
        }
        /*
         * updates the amount of money spent of a client into the database
         * if the operation failed it will return -1, otherwise it will return a positive number*/
        public int lowerMoneySpent(int clientId, double spent)
        {
            String visitorId = Convert.ToString(clientId);
            String sql = "UPDATE EVENT_ACCOUNT SET MoneySpent=MoneySpent-" + spent + " WHERE Visitor_id='" + visitorId + "'";
            MySqlCommand command = new MySqlCommand(sql, connection);

            try
            {
                connection.Open();
                int nrOfRecordsChanged = command.ExecuteNonQuery();
                return nrOfRecordsChanged;
            }
            catch
            {
                return -1; //which means the try-block was not executed succesfully, so  . . .
            }
            finally
            {
                connection.Close();
            }
        }

        /*
         * updates the no of units in stock for certain product into the database
         * if the operation failed it will return -1, otherwise it will return a positive number*/
        public int updateNoInStock(int productId, int stock)
        {
            String proId = Convert.ToString(productId);
            String sql = "UPDATE PRODUCT_HIRED SET ProductsInStock=" + stock + " WHERE ProductId='" + proId + "'";
            MySqlCommand command = new MySqlCommand(sql, connection);

            try
            {
                connection.Open();
                int nrOfRecordsChanged = command.ExecuteNonQuery();
                return nrOfRecordsChanged;
            }
            catch
            {
                return -1; //which means the try-block was not executed succesfully, so  . . .
            }
            finally
            {
                connection.Close();
            }
        }

    }
}
